package com.pennphoto.server;
import java.sql.*;

import com.pennphoto.client.*;
import com.pennphoto.shared.PennPhotoConstants;
public class QueryEngine {

	private static QueryEngine engine;
	private final Connection conn;
 
	
	private QueryEngine() throws Exception
	{
		 Class.forName ("oracle.jdbc.OracleDriver");

	     conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:XE",PennPhotoConstants.schemaName,PennPhotoConstants.dbpwd);
	     
	}
	
	public int getUserIdForCredentials(String username, String passwd) throws Exception
	{
		PreparedStatement pstmt = conn.prepareStatement("select userid from"+ PennPhotoConstants.schemaName +"users where email=? and passwd=?");
		pstmt.setString(1, username);
		pstmt.setString(2, passwd);
		ResultSet rset = pstmt.executeQuery();
		int userid=0;
		while(rset.next())
			userid=rset.getInt(1);
		return userid;
	}
	
	public PersonType getUserInformation(Integer userId) throws Exception
	{
		 ObjectFactory factory = new ObjectFactory();
		 PersonType person = factory.createPersonType();
		 PreparedStatement pstmt = conn.prepareStatement("SELECT firstname,lastname,dob,gender,address FROM "+ PennPhotoConstants.schemaName +"users  WHERE userid=?");
		 pstmt.setInt(1,userId);
		 ResultSet rset = pstmt.executeQuery();
		 while(rset.next())
		 {
			 String fname=rset.getString(1);
			 String lname=rset.getString(2);
			 person.setName(fname+" "+lname);
		 }
		 return person;
	}
	
	public static QueryEngine getInstance() throws Exception
	{
		if(engine==null)
			engine = new QueryEngine();
		return engine;
	}
	
}
